

***************************************************************************************************
***********************Minor do file: class size and teacher experience*****************************
***************************************************************************************************

*This .do file create two school-level varaibles: School stability rate and school VA

*Inputs: the main analysis dataset
*1. Teacher demographic data

*Output:
*1. "class_size_teacher_data.dta"
***************************************************************************************************
***************************************************************************************************
***************************************************************************************************

*Get all the teacher data
foreach i of numlist 7 8{
clear all
local j=`i'+1
import excel "/data/Teacher info/Teacher Demo 20072017.xlsx", sheet("200`i'-0`j'") firstrow
gen year=200`j'
destring OrganizationalUnit, replace
qui compress
save "/data_analysis/Eliso_Complete/teacher_0`j'_data.dta", replace
}
clear all
import excel "/data/Teacher info/Teacher Demo 20072017.xlsx", sheet("2009-10") firstrow
gen year=2010
destring OrganizationalUnit, replace
qui compress
save "/data_analysis/Eliso_Complete/teacher_10_data.dta", replace
foreach i of numlist 0 1 2 3 4 5 6{
clear all
local j=`i'+1
import excel "/data/Teacher info/Teacher Demo 20072017.xlsx", sheet("201`i'-1`j'") firstrow
if `i'<3{
destring OrganizationalUnit, replace
}
else if `i'>=4{
display "i is `i'"
}
gen year=201`j'
qui compress
save "/data_analysis/Eliso_Complete/teacher_1`j'_data.dta", replace
}

clear all
foreach i of numlist 7 8{
local j=`i'+1
append using "/data_analysis/Eliso_Complete/teacher_0`j'_data.dta"
erase  "/data_analysis/Eliso_Complete/teacher_0`j'_data.dta"
}
append using "/data_analysis/Eliso_Complete/teacher_10_data.dta"
erase "/data_analysis/Eliso_Complete/teacher_10_data.dta"
foreach i of numlist 0 1 2 3 4 5 6{
local j=`i'+1
append using  "/data_analysis/Eliso_Complete/teacher_1`j'_data.dta"
erase "/data_analysis/Eliso_Complete/teacher_1`j'_data.dta"
}
drop K
qui compress
replace YearsofExperience=YearsofExperiece if YearsofExperience==""
drop YearsofExperiece
duplicates drop teacherpseudoID year, force
save "/data_analysis/Eliso_Complete/teacher_all_data.dta", replace




*Create class size and teacher exp/probationary variables
clear all
foreach year of numlist 2003(1)2016{
clear all
use "/data_analysis/Gilraine_Pope/clean_byyear_dta/clean_`year'.dta"
keep stdpseudoid year schlocationcode tchpseudoidfrommarks
ren tchpseudoidfrommarks teacherid
egen classid   = group(teacherid year) 
egen class_size=count(stdpseudoid), by(classid year)
replace class_size=. if classid==. | class_size>40 | class_size<5

ren teacherid teacherpseudoID
destring teacherpseudoID, replace
merge m:1 teacherpseudoID year using "/data_analysis/Eliso_Complete/teacher_all_data.dta"
drop if _merge==2
drop _merge
save "/data_analysis/Eliso_Complete/tempCS_`year'.dta", replace
}

clear all
foreach year of numlist 2003(1)2016{
append using "/data_analysis/Eliso_Complete/tempCS_`year'.dta"
erase "/data_analysis/Eliso_Complete/tempCS_`year'.dta"
}

gen probationary=1 if EmployeeSubGroup!=""
replace probationary= 0 if EmployeeSubGroup=="Regular/Permanent"

replace YearsofExperience="10" if YearsofExperience=="10+"
destring YearsofExperience, replace

gen below5=(YearsofExperience<=5)
gen above10=(YearsofExperience==10)
replace below5=. if YearsofExperience==.
replace above10=. if YearsofExperience==.

ren schlocationcode locationcode
collapse class_size below5 above10 probationary, by(locationcode year)
qui compress
save "/data_analysis/Eliso_Complete/class_size_teacher_data.dta", replace

